package com.jwtter.generator.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.fastjson.JSON;
import com.jwtter.generator.db.jdbc.Column;
import com.jwtter.generator.db.jdbc.Table;
import com.jwtter.generator.utils.LogUtil;

/**
 * @date 2017年6月14日
 * @author jinwt
 *
 */
public class DBConnectionUtil {
	static String driverClassName = "oracle.jdbc.OracleDriver";
	static String url = "jdbc:oracle:thin:@10.10.68.91:1521:orcl";
	static String username = "pdfbtest";
	static String password = "pdfbtest";
	public static void main(String[] args) {
		Connection connection = getConnection(driverClassName, url, username, password);
		DatabaseMetaData data = getDatabaseMetaData(connection);
		getTables(data);
		// getCatalogs(data);
		// getTableTypes(data);
		// getSchemas(data);
		getColumns(data);
	}
	public static Connection getConnection(ConnectionInfo connectionInfo) {
		return getConnection(connectionInfo.getDriverClassName(), connectionInfo.getUrl(), connectionInfo.getUserName(), connectionInfo.getPassword());
	}
	public static Connection getConnection(String driverClassName, String url, String username, String password) {
		Connection connection = null;
		try {
			try {
				Class.forName(driverClassName);
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
			connection = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
			LogUtil.error("数据库连接失败", e);
		}
		return connection;
	}
	public static void close(Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
				LogUtil.error("数据库关闭失败", e);
			}
		}

	}

	public static DatabaseMetaData getDatabaseMetaData(Connection connection) {
		try {
			return connection.getMetaData();
		} catch (SQLException e) {
			e.printStackTrace();
			LogUtil.error("数据库连接获取DatabaseMetaData失败", e);
		}
		return null;

	}
	/**
	 * getTables 参数:catalog:目录名称，一般都为空.
	 * <p>
	 * 参数：schema:数据库名，对于oracle来说就用户名
	 * <p>
	 * 参数：tablename:表名称
	 * <p>
	 * 参数：type :表的类型"TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS",
	 * "SYNONYM".
	 * <p>
	 * 
	 * @param databaseMetaData
	 * @return
	 */
	public static List<Table> getTables(DatabaseMetaData databaseMetaData) {
		List<Table> list = new ArrayList<>();
		try {
			ResultSet resultSet = databaseMetaData.getTables(null, username.toUpperCase(), null, new String[]{"TABLE"});
			while (resultSet.next()) {
				Table table = new Table();
				table.setTABLE_CAT(resultSet.getString("TABLE_CAT"));
				table.setTABLE_SCHEM(resultSet.getString("TABLE_SCHEM"));
				table.setTABLE_NAME(resultSet.getString("TABLE_NAME"));
				table.setTABLE_TYPE(resultSet.getString("TABLE_TYPE"));
				table.setREMARKS(resultSet.getString("REMARKS"));
				// table.setTYPE_CAT(resultSet.getString("TYPE_CAT"));
				// table.setTYPE_SCHEM(resultSet.getString("TYPE_SCHEM"));
				// table.setTYPE_NAME(resultSet.getString("TYPE_NAME"));
				// table.setSELF_REFERENCING_COL_NAME(resultSet.getString("SELF_REFERENCING_COL_NAME"));
				// table.setREF_GENERATION(resultSet.getString("REF_GENERATION"));
				list.add(table);
			}
			LogUtil.info(JSON.toJSONString(list));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 获取表的类型 "SYNONYM","TABLE","VIEW"
	 * <p>
	 * "TABLE","VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY","LOCAL TEMPORARY", "ALIAS", "SYNONYM".
	 * 
	 * @param databaseMetaData
	 * @return
	 */
	public static List<String> getTableTypes(DatabaseMetaData databaseMetaData) {
		List<String> list = new ArrayList<>();
		try {
			ResultSet resultSet = databaseMetaData.getTableTypes();
			while (resultSet.next()) {
				list.add(resultSet.getString("TABLE_TYPE"));
			}
			LogUtil.info(JSON.toJSONString(list));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public static List<String> getCatalogs(DatabaseMetaData databaseMetaData) {
		List<String> list = new ArrayList<>();
		try {
			ResultSet resultSet = databaseMetaData.getCatalogs();
			while (resultSet.next()) {
				list.add(resultSet.getString("TABLE_CAT"));
			}
			LogUtil.info(JSON.toJSONString(list));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public static List<String> getSchemas(DatabaseMetaData databaseMetaData) {
		List<String> list = new ArrayList<>();
		try {
			ResultSet resultSet = databaseMetaData.getSchemas();
			while (resultSet.next()) {
				list.add(resultSet.getString("TABLE_SCHEM"));// 模式
				// list.add(resultSet.getString("TABLE_CATALOG"));
			}
			LogUtil.info(JSON.toJSONString(list));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public static List<Column> getColumns(DatabaseMetaData databaseMetaData) {
		List<Column> list = new ArrayList<>();
		try {
			ResultSet resultSet = databaseMetaData.getColumns(null, username.toUpperCase(), "CP_QUOTE", "FIRST_REMOTE_SUM_CHG_COMP_FEE");
			while (resultSet.next()) {
				Column column = new Column();
				column.setBUFFER_LENGTH(resultSet.getInt("BUFFER_LENGTH"));
				column.setCHAR_OCTET_LENGTH(resultSet.getInt("CHAR_OCTET_LENGTH"));
				column.setCOLUMN_DEF(resultSet.getString("COLUMN_DEF"));
				column.setCOLUMN_NAME(resultSet.getString("COLUMN_NAME"));// 字段名
				column.setCOLUMN_SIZE(resultSet.getInt("COLUMN_SIZE"));
				column.setDATA_TYPE(resultSet.getString("DATA_TYPE"));
				column.setDECIMAL_DIGITS(resultSet.getInt("DECIMAL_DIGITS"));
				// column.setIS_AUTOINCREMENT(resultSet.getString("IS_AUTOINCREMENT") != null ?
				// resultSet.getString("IS_AUTOINCREMENT") : null);
				// column.setIS_GENERATEDCOLUMN(resultSet.getString("IS_GENERATEDCOLUMN"));
				column.setIS_NULLABLE(resultSet.getString("IS_NULLABLE"));
				column.setNULLABLE(resultSet.getInt("NULLABLE"));
				column.setORDINAL_POSITION(resultSet.getInt("ORDINAL_POSITION"));
				// column.setSCOPE_CATALOG(resultSet.getString("SCOPE_CATALOG"));
				// column.setSCOPE_SCHEMA(resultSet.getString("SCOPE_SCHEMA"));
				// column.setSCOPE_TABLE(resultSet.getString("SCOPE_TABLE"));
				// column.setSOURCE_DATA_TYPE(resultSet.getShort("SOURCE_DATA_TYPE"));
				column.setSQL_DATA_TYPE(resultSet.getInt("SQL_DATA_TYPE"));
				column.setSQL_DATETIME_SUB(resultSet.getInt("SQL_DATETIME_SUB"));
				column.setTABLE_CAT(resultSet.getString("TABLE_CAT"));
				column.setTABLE_NAME(resultSet.getString("TABLE_NAME"));// 表明
				column.setTABLE_SCHEM(resultSet.getString("TABLE_SCHEM"));
				column.setTYPE_NAME(resultSet.getString("TYPE_NAME"));// 字段类型
				// column.set
				// column.set
				list.add(column);
				// list.add(resultSet.getString("TABLE_CATALOG"));
			}
			LogUtil.info(JSON.toJSONString(list));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
}
